package Model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Database.MySQLController;

public class Committee {

	private String id;
	private String title;
	private String purpose;
	private String rolesResponsibility;
	private String frequencyOfMeeting;
	private String date;
	private int status;
	private String approvedBy;
	private String createdBy;
	private ArrayList<String> staffList;
	private ArrayList<String> roles;
	
	public Committee() {
	}
	public Committee(String id, String title, String purpose,
			String rolesResponsibility, String frequencyOfMeeting, String date,
			int status, String createdBy, ArrayList<String> staffList) {
		super();
		this.id = id;
		this.title = title;
		this.purpose = purpose;
		this.rolesResponsibility = rolesResponsibility;
		this.frequencyOfMeeting = frequencyOfMeeting;
		this.date = date;
		this.status = status;
		this.createdBy = createdBy;
		this.staffList = staffList;
	}
	public Committee(String id, String title, String purpose,
			String rolesResponsibility, String frequencyOfMeeting, String date,
			int status, String createdBy, ArrayList<String> staffList,
			ArrayList<String> roles) {
		super();
		this.id = id;
		this.title = title;
		this.purpose = purpose;
		this.rolesResponsibility = rolesResponsibility;
		this.frequencyOfMeeting = frequencyOfMeeting;
		this.date = date;
		this.status = status;
		this.createdBy = createdBy;
		this.staffList = staffList;
		this.roles = roles;
	}
	public ArrayList<String> getStaffList() {
		return staffList;
	}
	public void setStaffList(ArrayList<String> staffList) {
		this.staffList = staffList;
	}
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getPurpose() {
		return purpose;
	}
	public void setPurpose(String purpose) {
		this.purpose = purpose;
	}
	public String getRolesResponsibility() {
		return rolesResponsibility;
	}
	public void setRolesResponsibility(String rolesResponsibility) {
		this.rolesResponsibility = rolesResponsibility;
	}
	public ArrayList<String> getRoles() {
		return roles;
	}
	public void setRoles(ArrayList<String> roles) {
		this.roles = roles;
	}
	public String getFrequencyOfMeeting() {
		return frequencyOfMeeting;
	}
	public void setFrequencyOfMeeting(String frequencyOfMeeting) {
		this.frequencyOfMeeting = frequencyOfMeeting;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}
	public String getApprovedBy() {
		return approvedBy;
	}
	public void setApprovedBy(String approvedBy) {
		this.approvedBy = approvedBy;
	}
	public String getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(String createdBy) {
		this.createdBy = createdBy;
	}
	
	/**
	 * This method getApprovedCommittees queries and retrieves list of committees that have been approved
	 * 
	 * @return committeeArrayList (ArrayList<Committee>)
	 */
	public static ArrayList<Committee> getApprovedCommittees()
	{
		ArrayList<Committee> commArr = new ArrayList<Committee>();
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		ResultSet rs = null;
		//	Status code for approval is 2, hence its parsed in as part of the selector in Committee_Status = 2
		String dbQuery = "SELECT * , group_concat(CommitteeRole_Name separator ', ') AS RoleList, group_concat(StaffNames separator ', ') AS StaffList FROM committee c INNER JOIN " +
				"committeerole cr ON c.Committee_ID = cr.Committee_ID " +
				"WHERE Committee_Status = 2 GROUP BY c.Committee_ID  ORDER BY Committee_Title;";
						try{
							rs = mysql.readRequest(dbQuery);
							while(rs.next())
							{
								ArrayList<String> staffName = new ArrayList<String>();
								ArrayList<String> roles = new ArrayList<String>();								
								String[] staffArr = rs.getString("StaffList").split(", ");
								String[] roleArr = rs.getString("RoleList").split(", ");
								Committee committee = new Committee(rs.getString("Committee_ID"),
										rs.getString("Committee_Title"),rs.getString("Committee_Purpose"),
										rs.getString("Committee_RolesResponsibility"),
										rs.getString("Committee_FrequencyofMeeting"),
										rs.getString("Committee_Date"),rs.getInt("Committee_Status"),
										rs.getString("Committee_CreatedBy"),staffName,roles);
								
								for(int i = 0; i < staffArr.length; i++)
								{
									System.out.println("Staff Login ID : " + staffArr[i]);
									String dbQueryStaffName = "SELECT * FROM staff WHERE Staff_LoginID = '" + staffArr[i] + "';";
									ResultSet rs2 = mysql.readRequest(dbQueryStaffName);
									while(rs2.next()){
									staffName.add(rs2.getString("Staff_Name")); 
									}
									System.out.println("My Staff Name is : " + staffName.get(i).toString());
								}
								for(int i = 0 ; i < roleArr.length;i++)
								{
									roles.add(roleArr[i]);
								}
								committee.setStaffList(staffName);
								commArr.add(committee);
							}
						}catch(SQLException sqlErr)
						{
							sqlErr.printStackTrace();
						}catch(ArrayIndexOutOfBoundsException arrayErr)
						{
							System.out.println(arrayErr.getLocalizedMessage());
						}catch(IndexOutOfBoundsException indexOBEx)
						{
							System.out.println("System at model forgot to check bounds of arrays");
						}catch(NullPointerException nullPtrEx)
						{
							System.out.println("ArrayList retrieved is empty! Please ensure that the database contains data");
							System.out.println(nullPtrEx.getLocalizedMessage());
						}finally
						{
							mysql.terminate();
						}
						return commArr;
	}
	
	
	/**
	 * 
	 * @return
	 */
	public static ArrayList<Committee> getUnapprovedCommittees()
	{
		ArrayList<Committee> commArr = new ArrayList<Committee>();
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		ResultSet rs = null;
		String dbQuery = "SELECT * ,group_concat(CommitteeRole_Name separator ' ') AS RoleList, group_concat(StaffNames separator ', ') AS StaffList FROM committee c INNER JOIN " +
"committeerole cr ON c.Committee_ID = cr.Committee_ID " +
"WHERE Committee_Status < 0 GROUP BY c.Committee_ID  ORDER BY Committee_Title;";
		try{
			rs = mysql.readRequest(dbQuery);
			while(rs.next())
			{
				ArrayList<String> staffName = new ArrayList<String>();
				ArrayList<String> roles = new ArrayList<String>();
				String[] staffArr = rs.getString("StaffList").split(", ");
				String[] roleArr = rs.getString("RoleList").split(" ");
				Committee committee = new Committee(rs.getString("Committee_ID"),
						rs.getString("Committee_Title"),rs.getString("Committee_Purpose"),
						rs.getString("Committee_RolesResponsibility"),
						rs.getString("Committee_FrequencyofMeeting"),
						rs.getString("Committee_Date"),rs.getInt("Committee_Status"),
						rs.getString("Committee_CreatedBy"),staffName,roles);
				
				for(int i = 0; i < staffArr.length; i++)
				{
					System.out.println("Staff Login ID : " + staffArr[i]);
					String dbQueryStaffName = "SELECT * FROM staff WHERE Staff_LoginID = '" + staffArr[i] + "';";
					ResultSet rs2 = mysql.readRequest(dbQueryStaffName);
					while(rs2.next()){
					staffName.add(rs2.getString("Staff_Name")); 
					}
					System.out.println("My Staff Name is : " + staffName.get(i).toString());
				}
				for(int i = 0 ; i < roleArr.length; i++)
				{
					roles.add(roleArr[i]);
				}
				committee.setStaffList(staffName);
				commArr.add(committee);
			}
		}catch(SQLException sqlErr)
		{
			sqlErr.printStackTrace();
		}catch(ArrayIndexOutOfBoundsException arrayErr)
		{
			System.out.println(arrayErr.getLocalizedMessage());
		}catch(IndexOutOfBoundsException indexOBEx)
		{
			System.out.println("System at model forgot to check bounds of arrays");
		}catch(NullPointerException nullPtrEx)
		{
			System.out.println("ArrayList retrieved is empty! Please ensure that the database contains data");
			System.out.println(nullPtrEx.getLocalizedMessage());
		}finally
		{
			mysql.terminate();
		}
		return commArr;
	}
	
	public boolean removeCommittees(String committeeID)
	{
		boolean success = false;
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "DELETE FROM committee WHERE Committee_ID ='" + committeeID + "'";
		try{
			if(mysql.updateRequest(dbQuery) == 1)
			{
				success = true;
			}
		}catch(SQLException sqlErr)
		{
			System.out.println(sqlErr.getLocalizedMessage());
		}finally
		{
			mysql.terminate();
		}
		return success;
	}
	
	/**
	 * Allows bulk removal of committee.
	 * @param committeeIDList
	 * @return true if committee removed successfully, false if unsuccessful
	 */
	public static boolean removeCommittees(String[] committeeIDList)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "DELETE FROM committee WHERE Committee_ID IN (";
		System.out.println("committeeIDList length is : " + committeeIDList.length);
		for(int i = 0 ; i < committeeIDList.length;i++)
		{
		
			if(committeeIDList.length == 1)
			{
				dbQuery += "'" + committeeIDList[i] + "');";
				break;
			}
			if(i == committeeIDList.length - 1)
			{
				dbQuery += "'" + committeeIDList[i] + "');";
			}else
			{
				dbQuery += "'" + committeeIDList[i] + "',";
			}
			System.out.println(dbQuery);
		}
		try{
			if(mysql.updateRequest(dbQuery) > 0)
			{
				return true;
			}
		}catch(SQLException sqlErr)
		{
			sqlErr.printStackTrace();
		}finally
		{
			mysql.terminate();
		}
		return false;
	}
	
	public ArrayList<Committee> getCommitteesForStaff(String staffLoginID)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "SELECT * FROM qmsdb.committee c INNER JOIN committeerole " +
				"cr ON c.Committee_ID = cr.Committee_ID WHERE StaffNames LIKE '" + staffLoginID + "';";
		ResultSet rs = null;
		ArrayList<Committee> comList = new ArrayList<Committee>();
		try{
			rs = mysql.readRequest(dbQuery);
			while(rs.next())
			{
				ArrayList<String> staffName = new ArrayList<String>();
				ArrayList<String> roles = new ArrayList<String>();
				String[] staffArr = rs.getString("StaffList").split(", ");
				String[] roleArr = rs.getString("RoleList").split(" ");
				Committee committee = new Committee(rs.getString("Committee_ID"),
						rs.getString("Committee_Title"),rs.getString("Committee_Purpose"),
						rs.getString("Committee_RolesResponsibility"),
						rs.getString("Committee_FrequencyofMeeting"),
						rs.getString("Committee_Date"),rs.getInt("Committee_Status"),
						rs.getString("Committee_CreatedBy"),staffName,roles);
				comList.add(committee); 
			}
		}catch(SQLException sqlEx)
		{
			System.out.println("Query not well formed. Dumping query and trace stack...\n" + dbQuery);
			sqlEx.printStackTrace();
		}
		return new ArrayList<Committee>();
	}
	
	public Committee getCommittee(String committeeName)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "SELECT * FROM committee "
				+ "INNER JOIN committeerole ON committeeID = committee_Id "
				+ "WHERE committeeName ='" + committeeName + "';";
		Committee com = new Committee();
		ResultSet rs = null;
		try{
			rs = mysql.readRequest(dbQuery);
			if(rs.next())
			{
				com.setId(rs.getString("Committee_ID"));
				com.setTitle(rs.getString("Committee_Title"));
				com.setPurpose(rs.getString("Committee_Purpose"));
				com.setRolesResponsibility(rs.getString("Committee_RolesResponsibility"));
				com.setRoles(roles);
			}
		}catch(SQLException sqlErr)
		{
			sqlErr.printStackTrace();
		}
		return com;
	}


}
